package com.h.ssRoom.repo;

import java.util.List;
<<<<<<< HEAD
=======
import java.util.Map;
>>>>>>> 71d11a30821e4a369fe163d5c0d88106681576b3

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import com.h.ssRoom.model.Customers;

public interface CustomersRepo extends JpaRepository<Customers, Integer>{
<<<<<<< HEAD
	@Query(value = "select * from Customers",nativeQuery = true)
    public List<Customers> findAllCustomers();

    //根据顾客主键查询剩余时长
=======
	//根据顾客主键查询剩余时长
>>>>>>> 71d11a30821e4a369fe163d5c0d88106681576b3
    @Query(value = "select mem_length from customers where customerID=?1", nativeQuery = true)
    public Integer getMemLenByID(String customerID);  
    
    //根据用户编号获取用户信息
    @Query(value = "select * from customers where customerid=?1",nativeQuery = true)
    public List<Customers> getCustomersByCustomerID(String customerID);
    
    //更新用户信息
    @Transactional
    @Modifying
<<<<<<< HEAD
    @Query(value = "update customers set mem_length=?2,res_status=?3,customer_name=?4 where customerID=?1",nativeQuery = true)
    public void updateCustomers(String customerID, Integer memLength, Integer resStatus,String customerName);
=======
    @Query(value = "update customers set mem_length=?2,res_status=?3 where customerID=?1",nativeQuery = true)
    public void updateCustomers(String customerID,Integer memLength,Integer resStatus);
>>>>>>> 71d11a30821e4a369fe163d5c0d88106681576b3
    
    //删除用户
    @Transactional
    @Modifying
    @Query(value = "delete from customers where customerID=?1",nativeQuery = true)
    public void deleteCustomers(String customerID);
<<<<<<< HEAD
=======

    //获取用户学习总时长
    @Query(value = "SELECT SUM(appoints.study_len) AS total_study_len FROM customers JOIN appoints ON customers.customerid=appoints.customerid WHERE customers.customerid=?1",nativeQuery=true)
    public Integer getSumStudyLength(String customerID);
    
    //获取用户每次学习时长和学习时间
    @Query(value = "SELECT date_format(start_time,'%d') as date,study_len FROM appoints WHERE customerid=?1 ORDER BY date ASC",nativeQuery=true)
    public List<Map<String,Object>> getPerStudyRecord(String customerID);
>>>>>>> 71d11a30821e4a369fe163d5c0d88106681576b3
}
